package cn.biplam.back.jda;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import cn.biplam.back.jda.jdbc.JdbcConnection;
import cn.biplam.back.jda.jdbc.JdbcConnectionFactory;
import cn.biplam.common.bean.sqlengine.Sql;
import cn.biplam.common.client.DbResource;
import cn.biplam.common.tools.DEBUG;
import cn.biplam.common.tools.TOOLS;
import cn.biplam.common.wrapper.ErrorMessage;
import cn.biplam.common.wrapper.sqlresult.SqlResult;

/**
 * Java数据库访问类(包括连接池方式)
 * @author 	WangSong 2009-3-11 下午05:29:24
 * @version 1.0
 */
public class Jda {

	/**数据库连接*/
	private Connection conn;
	private boolean autoClose = true;

	/**
	 * 构造方法
	 * @param mDbResource  数据源信息
	 * @throws ErrorMessage 
	 */
	public Jda(DbResource mDbResource) throws ErrorMessage {
		init(mDbResource);
	}

	/**
	 * 构造方法
	 * @param mDbResource  数据源信息
	 * @param connClose    是否执行ddl或dml后自动关闭连接。false需要调用jda的connClose()方法手动关闭连接
	 * @throws ErrorMessage 
	 */
	public Jda(DbResource mDbResource,boolean connClose) throws ErrorMessage {
		this.autoClose = connClose;
		init(mDbResource);
	}

	/**
	 * 构造方法
	 * @param mDbResource  数据源信息
	 * @throws ErrorMessage 
	 */
	public Jda(DbResource mDbResource,int iQueryModel,String sUserID,String sPageId) throws ErrorMessage {
		init(mDbResource);
	}

	private void init(DbResource mDbResource) throws ErrorMessage{
		boolean connectPool=false;
		if(mDbResource==null){
			throw new ErrorMessage("DbResource is null!");
		}
		if("2".equals(mDbResource.getDsType().trim())){
			connectPool=true;
			/**数据源类型为连接池*/
			try {
				this.getDefaultConnection(mDbResource.getDsname());
				return;
			} catch (NamingException e) {
				//此处不做处理,后面尝试JDBC直接连接
				DEBUG.println("【Jda Warnning】Connect to pool failed!");
			}
		}
		if(connectPool){
			DEBUG.println("【Jda Warnning】Try jdbc connect!");
		}
		/**数据源类型为jdbc*/
		JdbcConnection mJdbcConnection = JdbcConnectionFactory.getInstance(mDbResource);
		this.conn = mJdbcConnection.getConnection();
	}

	/**
	 * 查询操作
	 * @param  mSql  sql资源实例
	 * @return SqlResult
	 * @throws ErrorMessage 
	 */
	public SqlResult executeSelect(Sql mSql) throws ErrorMessage {

		ResultSet rs=null;
		Statement stmt=null;
		SqlResult sr = new SqlResult(1);

		try{
			if(conn!=null){
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(mSql.toString());
				if(rs!=null){
					sr.put(rs);
				}
			}	
		}catch(ErrorMessage e){
			throw e;
		}catch(Exception e){
			ErrorMessage me=new ErrorMessage(e,"Jda.executeSelect failed");
			setMessage(me,mSql.toString());
			throw me;
		}finally{
			close(rs, stmt, conn);
		}
		return sr;
	}
	
	/**
	 * 设置消息,追加sql文本到Sql异常消息后(判断消息是否\n结束)
	 * @param me
	 * @param sql
	 */
	private void setMessage(ErrorMessage me,String sql){
		StringBuilder sb = new StringBuilder(me.getMessage());
		if(sb.charAt(sb.length()-1)=='\n'){
			sb.append("\tat ");
		}else{
			sb.append("\n\tat ");
		}
		sb.append(sql);
		me.setMessage(sb.toString());

	}

	/**
	 * 查询操作
	 * @param  mSql  sql资源实例
	 * @return SqlResult
	 * @throws ErrorMessage 
	 */
	public SqlResult executeSelect(Sql mSql,int resultSize) throws ErrorMessage {

		ResultSet rs=null;
		Statement stmt=null;
		SqlResult sr = new SqlResult(1);

		try{
			if(conn!=null){
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(mSql.toString());
				if(rs!=null){
					sr.put(rs,resultSize);
				}
			}	
		}catch(ErrorMessage e){
			throw e;
		}
		catch(Exception e){
			ErrorMessage me=new ErrorMessage(e,"Jda.executeSelect failed");
			setMessage(me, mSql.toString());
			throw me;
		}finally{
			close(rs, stmt, conn);
		}
		return sr;
	}

	/**
	 * 查询操作(分页)
	 * @param  mSql  sql资源实例
	 * @param  start 开始位置
	 * @param  end   结束位置
	 * @return SqlResult
	 * @throws ErrorMessage 
	 */
	public SqlResult executeSelect(Sql mSql,int start,int end) throws ErrorMessage {

		ResultSet rs=null;
		Statement stmt=null;
		SqlResult sr = new SqlResult(1);

		try{
			if(conn!=null){
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(mSql.toString());
				if(rs!=null){
					sr.put(rs,start,end);
				}
			}	
		}catch(ErrorMessage e){
			throw e;
		}
		catch(Exception e){
			ErrorMessage me=new ErrorMessage(e,"Jda.executeSelect failed");
			setMessage(me, mSql.toString());
			throw me;
		}finally{
			close(rs, stmt, conn);
		}
		return sr;
	}

	/**
	 * 执行 create  drop 等不能rollback的 sql的方法
	 * @param  mSql  sql操作类
	 * @return boolean
	 */
	public boolean executeDDL (Sql mSql) throws ErrorMessage {

		boolean boo = true;
		Statement stmt=null;

		try{
			if(conn!=null){
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_READ_ONLY);
				stmt.executeUpdate(mSql.toString());
			}else{
				boo=false;
			}

		}catch(Exception e){ 
			ErrorMessage me=new ErrorMessage(e,"Jda.executeDDL failed");
			setMessage(me, mSql.toString());
			throw me;
		}finally{
			close(null, stmt, conn);
		}
		return boo;
	}

	/**
	 * 执行 delete  update insert等 可以rollback的 sql的方法
	 * @param  mSql  sql操作类
	 * @return boolean
	 */
	public boolean executeDML (Sql mSql) throws ErrorMessage {

		boolean boo = true;
		Statement stmt=null;

		try{
			if(conn!=null){
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_READ_ONLY);
				stmt.executeUpdate(mSql.toString());
			}else{
				boo=false;
			}
		}catch(Exception e){ 
			ErrorMessage me=new ErrorMessage(e,"Jda.executeDML failed");
			setMessage(me, mSql.toString());
			throw me;
		}finally{
			close(null, stmt, conn);
		}
		return boo;
	}
	
	/**
	 * 批量更新
	 * @param sql			SQL语句集合
	 * @return boolean
	 * @throws ErrorMessage 
	 */
	public boolean executeDMLBatch(List<String> sqls) throws ErrorMessage{
		boolean boo = true;
		Statement stmt=null;

		try{
			if(conn!=null){
				conn.setAutoCommit(false);
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_READ_ONLY);
				for(String sql: sqls){
					stmt.addBatch(sql);
				}
				stmt.executeBatch();
				conn.commit();
			}else{
				boo=false;
			}
		}catch(SQLException e){
			try{
				conn.rollback();
			}catch(SQLException se){
				throw new ErrorMessage(se);
			}
			ErrorMessage me=new ErrorMessage(e,"Jda.executeDMLBatch failed");
			StringBuilder sb = new StringBuilder();
			for(String ite:sqls){
				sb.append(ite);
				sb.append("\n\t   ");
			}
			sb.delete(sb.length()-2, sb.length());
			TOOLS.deleteStringBuilderLast(sb,3);
			setMessage(me, sb.toString());
			throw me;
		}finally{
			close(null, stmt, conn);
		}
		return boo;	
	}

	/**
	 * 批量更新
	 * @param sql			SQL语句集合
	 * @return boolean
	 */
	public boolean executeDMLBatch(String[] sql) throws ErrorMessage {

		boolean boo = true;
		Statement stmt=null;

		try{
			if(conn!=null){
				conn.setAutoCommit(false);
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_READ_ONLY);
				for(int i=0;i<sql.length;i++){
					stmt.addBatch(sql[i]);
				}
				stmt.executeBatch();
				conn.commit();
			}else{
				boo=false;
			}
		}catch(SQLException e){
			try{
				conn.rollback();
			}catch(SQLException se){
				throw new ErrorMessage(se);
			}
			ErrorMessage me=new ErrorMessage(e,"Jda.executeDMLBatch failed");
			StringBuilder sb = new StringBuilder();
			for(String ite:sql){
				sb.append(ite);
				sb.append("\n\t   ");
			}
			sb.delete(sb.length()-2, sb.length());
			TOOLS.deleteStringBuilderLast(sb,3);
			setMessage(me, sb.toString());
			throw me;
		}finally{
			close(null, stmt, conn);
		}
		return boo;	
	}

	/**
	 * 取得默认数据源连接，连接池方式数据源
	 * @param  dbSource  数据源信息
	 * @throws NamingException 
	 */
	private void getDefaultConnection(String dbSource) throws ErrorMessage, NamingException {

		Context initCtx = new InitialContext();
		Context ctx = (Context) initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)ctx.lookup(dbSource);
		try {
			this.conn = ds.getConnection();
		} catch (SQLException e) { 
			throw new ErrorMessage(e,"GetDefaultConnection failed!");
		} 
	}

	/**
	 * 返回数据库连接
	 * @return Connection
	 */
	public Connection getConn(){
		return this.conn;
	}

	/**
	 * 关闭数据库连接
	 * @throws ErrorMessage 
	 */
	public void connClose() throws ErrorMessage{
		try {
			this.conn.close();
		} catch (SQLException e) {
			ErrorMessage mErrorMessage = new ErrorMessage(e,"Jda.connClose failed ");
			throw mErrorMessage;
		}
	}

	/**
	 * 关闭数据库连接
	 * @param rs   数据集
	 * @param stmt Statement
	 * @param conn 数据库连接
	 */
	private void close(ResultSet rs,Statement stmt,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null && autoClose){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
